package com.deng.performance.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.deng.common.SQLHelper;
import com.deng.common.vo.DispenseOrganizationVo;
import com.deng.common.vo.EmployeeVo;
import com.deng.etl.impl.WorkLoadMoveToBack;
import com.deng.performance.dao.IDispenseFirstBonus;
import com.deng.performance.vo.OrganizationFirstBonusVo;
import com.deng.util.DateTools;
import com.deng.util.GlobalProperties;

import flex.messaging.io.ArrayCollection;

public class DispenseFirstBonusImpl extends SQLHelper implements
		IDispenseFirstBonus {
	
	public List<DispenseOrganizationVo> getAllDispenseOrganization(Connection conn)
	{
		try
		{
			String sql = "select t_sys_stat_dispense.id,t_sys_stat_dispense.name,t_sys_stat_dispense.duty_id," +
					" t_sys_stat_organization_vs_dispense_organization.stat_id" +
					" from dbo.t_sys_stat_dispense" +
					" left join t_sys_stat_organization_vs_dispense_organization" +
					" on t_sys_stat_dispense.id = t_sys_stat_organization_vs_dispense_organization.dispense_id" +
					" order by t_sys_stat_dispense.id";
			ResultSet rs = queryData(conn,sql);
			List<DispenseOrganizationVo> l = new ArrayList<DispenseOrganizationVo>();
			DispenseOrganizationVo tmpVo = null;
			while(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				int duty_id = rs.getInt("duty_id");
				int stat_id = rs.getInt("stat_id");
				if(tmpVo != null && id == tmpVo.id)//同一个节点
				{
					tmpVo.new_related_id += ":"+stat_id+":";
				}
				else//两种情况：
				{
					if(tmpVo != null)//第一次  tmpVo＝null    ；//与前一节点不同 tmpVo！＝null and id不相同
					{
						l.add(tmpVo);					
					}
					tmpVo = new DispenseOrganizationVo();
					tmpVo.id = id;
					tmpVo.name = name;
					tmpVo.duty_id = duty_id;
					if(stat_id != 0)
					{
						tmpVo.new_related_id = ":"+stat_id+":";
					}
				}
			}
			if(tmpVo != null)
			{
				l.add(tmpVo);
			}			
			return l;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return null;
		
	}
	public List<DispenseOrganizationVo> getAllDispenseFirstBonus(
			String statMonth) {
		try{

//			String sql = " select t_sys_stat_organization_vs_dispense_organization.id, " +
//			"   t_sys_stat_organization_vs_dispense_organization.name," +
//			"	t_sys_stat_organization_vs_dispense_organization.formula as formula_new," +
//			"	t_stat_dispense_month.formula as formula_old," +
//			"	t_stat_dispense_month.cal_value,t_stat_dispense_month.pay_value," +
//			"	t_stat_dispense_month.put_value, t_stat_dispense_month.valid," +
//			"	t_sys_stat_organization_vs_dispense_organization.related_organization_id as new_related_id," +
//			"	t_stat_dispense_month.related_organization_id as old_related_id," +
//			"	'"+statMonth+"' as stat_time" +
//			"	 from t_sys_stat_organization_vs_dispense_organization" +
//			"	left join t_stat_dispense_month" +
//			"	on t_sys_stat_organization_vs_dispense_organization.id = t_stat_dispense_month.dispense_id" +
//			"	and stat_time = '"+statMonth+"'" +
//			"	order by  t_sys_stat_organization_vs_dispense_organization.name";
			
			Connection conn = getConnect();
			String sql = "select '"+statMonth+"' as statMonth,k.id,k.name,k.duty_id,k.stat_id," +
					" m.related_organization_id as old_related_id,m.formula as formula_old," +
					" m.cal_value,m.pay_value,m.put_value,m.duty_id,m.create_time,m.valid from" +
					" (" +
					" select t_sys_stat_dispense.id,t_sys_stat_dispense.name,t_sys_stat_dispense.duty_id," +
					" t_sys_stat_organization_vs_dispense_organization.stat_id" +
					" from dbo.t_sys_stat_dispense" +
					" left join t_sys_stat_organization_vs_dispense_organization" +
					" on t_sys_stat_dispense.id = t_sys_stat_organization_vs_dispense_organization.dispense_id" +
					" ) as k" +
					" left join t_stat_dispense_month as m" +
					" on k.id = m.dispense_id" +
					" and stat_time = '"+statMonth+"'" +
					" order by  k.name";
			ResultSet rs = queryData(conn,sql);
			List<DispenseOrganizationVo> l = new ArrayList<DispenseOrganizationVo>();
			DispenseOrganizationVo tmpVo = null;
			while(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				int duty_id = rs.getInt("duty_id");
				int stat_id = rs.getInt("stat_id");
				String cal_value = rs.getBigDecimal("cal_value")!=null?rs.getBigDecimal("cal_value").toString():"0";
				String put_value = rs.getBigDecimal("put_value")!=null?rs.getBigDecimal("put_value").toString():"0";
				String pay_value = rs.getBigDecimal("pay_value")!=null?rs.getBigDecimal("pay_value").toString():"0";
//				vo.formula_new = rs.getString("formula_new");
				String formula_old = rs.getString("formula_old");
				String old_related_id = rs.getString("old_related_id");
				int valid = rs.getInt("valid");
				
				if(tmpVo != null && id == tmpVo.id)//同一个节点,需叠加
				{
					tmpVo.new_related_id += ":"+stat_id+":";
				}
				else//两种情况：
				{
					if(tmpVo != null)//第一次  tmpVo＝null    ；//与前一节点不同 tmpVo！＝null and id不相同
					{
						l.add(tmpVo);					
					}
					tmpVo = new DispenseOrganizationVo();
					tmpVo.id = id;
					tmpVo.name = name;
					tmpVo.duty_id = duty_id;
					tmpVo.cal_value = cal_value;
					tmpVo.put_value = put_value;
					tmpVo.pay_value = pay_value;
					tmpVo.valid = valid;
					tmpVo.formula_old = formula_old;
					tmpVo.old_related_id = old_related_id;
					tmpVo.stat_time_str = statMonth;
					if(stat_id != 0)
					{
						tmpVo.new_related_id = ":"+stat_id+":";
					}
				}
			}
			if(tmpVo != null)
			{
				l.add(tmpVo);
			}
			
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public List<DispenseOrganizationVo> calAllDispenseFirstBonus(
			List<DispenseOrganizationVo> dvList) {
		try{
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public void saveAllDispenseFirstBonus(List<DispenseOrganizationVo> dvList) {
		try{
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public void saveAllDispenseFirstBonusMonth(String stat_time) {
		try{
			Connection conn = getConnect();
			String sql = "insert into t_stat_dispense_record_month " +
					"	select * from t_stat_dispense_recent" +
					"	where stat_time = '"+stat_time+"'";
			updateData(conn,sql);
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public List<DispenseOrganizationVo> calAllDispenseFirstBonusByOrganizationRecent(
			List<DispenseOrganizationVo> dvList) {

		try{
			if(dvList == null){
				return null;
			}
			Connection conn = getConnect();
			for(int i =0;i<dvList.size();i++){
				DispenseOrganizationVo vo = dvList.get(i);
				if(vo.valid == 1){
					continue;
				}
				if(vo.new_related_id == null || vo.new_related_id.trim().length()==0){
					continue;
				}
				String s[] = vo.new_related_id.replaceAll("::", ":").split(":");
				BigDecimal cal_value = new BigDecimal(0);
				BigDecimal pay_value = new BigDecimal(0);
//				String new_formula = "";
				for(int x = 0;x<s.length;x++){
					try{
						if(s[x] == null || s[x].equals("")){
							continue;
						}
						ResultSet rs = queryData(conn,"select * from t_stat_department_month" +
								"	where organization_id = "+s[x]+
								" 	and stat_time = '"+vo.stat_time_str+"'");
						if(rs.next()){
//							System.out.println(rs.getBigDecimal("cal_value")+ "........."+rs.getBigDecimal("pay_value"));
							cal_value = cal_value.add(rs.getBigDecimal("cal_value") != null? 
										rs.getBigDecimal("cal_value"):new BigDecimal(0));
							pay_value = pay_value.add(rs.getBigDecimal("pay_value") != null?
										rs.getBigDecimal("pay_value"):new BigDecimal(0));
							String tmpFunction = rs.getString("base_bonus_function_detail");
//							if(tmpFunction != null){
//								new_formula+= tmpFunction.replaceAll("'", "''")+"+";
//							}
							
						}
					}catch(Exception ex){
						ex.printStackTrace();
					}
				}
				String searchSql = "select * from t_stat_dispense_month where dispense_id = "+vo.id +
					" and stat_time = '"+vo.stat_time_str+"'";
				ResultSet rs2 = queryData(conn, searchSql);
				String updateSql = "";
				if(rs2.next()){
					updateSql = "update t_stat_dispense_month " +
							"	set cal_value = "+cal_value+", " +
							"	pay_value = "+pay_value+",put_value = "+cal_value+
//							",formula="+
//								(new_formula.length()>1?"'"+new_formula.substring(0, new_formula.length()-1)+"'":"NULL")+
							"	where dispense_id = "+vo.id +
							" 	and stat_time = '"+vo.stat_time_str+"'";
				}else{
					updateSql = "insert into t_stat_dispense_month" +
							"	(dispense_id,name,stat_time," +
							"	related_organization_id," +
//							"formula," +
							"cal_value," +
							"	put_value,pay_value,valid)" +
							"	values("+vo.id+",'"+vo.name+"','"+vo.stat_time_str+"'," +
							"	'"+vo.new_related_id+"',"+
//								(new_formula.length()>1?"'"+new_formula.substring(0, new_formula.length()-1)+"'":"NULL")+","+
								cal_value+"," +
							"	"+cal_value+","+pay_value+",0)";
				}
				updateData(conn, updateSql);
				vo.cal_value = cal_value.toString();
				vo.pay_value = pay_value.toString();
				vo.put_value = cal_value.toString();
			}
			freeConnection(conn);
			return dvList;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	
	}

	public DispenseOrganizationVo recalByNewFunctionForAllOrg(
			Connection conn,DispenseOrganizationVo vo) {
		try{			
			System.out.println("以最新方式计算单元奖金，将重新计算合并单元奖金。");
			if(vo.new_related_id == null || vo.new_related_id.equals("") ){
				DispenseOrganizationVo rtVo = new DispenseOrganizationVo();
				rtVo.status = "请检查相关工作绩效核算科室是否配置";
				return rtVo;
			}
			String newRelatedId = vo.new_related_id.replaceAll("::", ":");
			String[] s= newRelatedId.split(":");
			if(s.length <= 0){
				DispenseOrganizationVo rtVo = new DispenseOrganizationVo();
				rtVo.status = "请检查相关工作绩效核算科室是否配置";
				return rtVo;
			}
			
			String stat_time = vo.stat_time_str;			
			String sql1 = "select dbo.GetFullOrganizationName(a.id) as fName," +
					"	a.base_bonus_function_detail as new_function," +
					"	a.duty_id" +
					"	from t_sys_stat_department  as a" +
					"	where a.id = ";
			BigDecimal bt = new BigDecimal("0");
			BigDecimal putV = new BigDecimal("0");
			BigDecimal payV = new BigDecimal("0");
			String totalFormula = "";
			
			for(int i =0;i<s.length;i++){
				if(s[i].equals("")){
					continue;
				}
				OrganizationFirstBonusVo ovo = new OrganizationFirstBonusVo();
				ovo.organization_id = Integer.valueOf(s[i]);
				ResultSet rs = queryData(conn, sql1+ovo.organization_id);
				if(rs.next()){
					ovo.full_name = rs.getString("fName");
					ovo.new_function = rs.getString("new_function");
					if(ovo.new_function != null && !ovo.new_function.equals("")){
						totalFormula += ovo.new_function+"+";
					}					
					ovo.duty_id = rs.getInt("duty_id");					
				}
				ovo.stat_time = DateTools.stdJustDate(stat_time);
				OrganizationFirstBonusVo rtVo = new OrganizationFirstBonusImpl().recalByNewFunction(conn,ovo,1);
				if(rtVo == null){
					System.out.println("没有找到关联科室！！  可检查DispenseFirstBonusImpl");
					continue;
				}
				String rtValue = rtVo.cal_value;
				String putValue = rtVo.put_value;
				String payValue = rtVo.pay_value;
				if(rtValue != null && !rtValue.equals("")){
					bt = bt.add(new BigDecimal(rtValue));
				}else{
					bt.add(new BigDecimal("0"));
				}
				
				if(putValue != null && !putValue.equals("")){
					putV =putV.add(new BigDecimal(putValue));
				}else{
					putV.add(new BigDecimal("0"));
				}
				
				if(payValue != null && !payValue.equals("")){
					payV = payV.add(new BigDecimal(payValue));
				}else{
					payV.add(new BigDecimal("0"));
				}
			}
			vo.cal_value = bt.toString();
			vo.pay_value = payV.toString();
			vo.put_value = putV.toString();
			if(totalFormula == null || "".equals(totalFormula))
			{
				vo.status = "没有设置公式，请检查后重新计算。";
				return vo;
			}
			vo.formula_new = totalFormula.substring(0, totalFormula.length()-1);
			
			String sql = "select * from t_stat_dispense_month" +
					"	where dispense_id = "+vo.id+
					"	and stat_time = '"+vo.stat_time_str+"'" +
					"  order by name";
			PreparedStatement pst = 
				conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			ResultSet rsx = pst.executeQuery();
			if(rsx.next()){
//				===================================SQL2005语句
//				if(rsx.getInt("valid") == 0){
//					rsx.updateString("related_organization_id", vo.new_related_id+"");
//					rsx.updateString("formula", vo.formula_new.replaceAll("'", "''"));
//					rsx.updateBigDecimal("cal_value", bt);
//					rsx.updateBigDecimal("pay_value", bt);
//					rsx.updateBigDecimal("put_value", bt);
//					rsx.updateInt("duty_id", vo.duty_id);
//				}else{
//					rsx.updateString("related_organization_id", vo.new_related_id+"");
//					rsx.updateString("formula", vo.formula_new.replaceAll("'", "''"));
//					rsx.updateBigDecimal("cal_value", bt);
//				}				
//				rsx.updateRow();
//				conn.commit();
//				===================================SQL2000语句
				String updateSql = "";
				if(rsx.getInt("valid") == 0){
					updateSql = "update t_stat_dispense_month set related_organization_id = '"+vo.new_related_id+"'," +
							"formula = '"+vo.formula_new.replaceAll("'", "''")+"'," +
							"cal_value="+bt+"," +
							"pay_value="+payV+"," +
							"put_value="+putV+"," +
							"duty_id="+vo.duty_id+
							" where stat_time = '"+vo.stat_time_str+"' and dispense_id = "+vo.id; 
					updateData(conn, updateSql);
				}else{
					updateSql = "update t_stat_dispense_month set related_organization_id = '"+vo.new_related_id+"'," +
						"formula = '"+vo.formula_new.replaceAll("'", "''")+"'," +
						"cal_value="+bt+"," +
						"put_value="+putV+"" +
						" where stat_time = '"+vo.stat_time_str+"' and dispense_id = "+vo.id; 
					updateData(conn, updateSql);
				}
			}else{
				String updateSql = "insert into t_stat_dispense_month" +
					"	(dispense_id,name,stat_time," +
					"	related_organization_id,formula," +
					"	cal_value,pay_value,put_value," +
					"	duty_id,create_time,valid) " +
					"	values("+vo.id+",'"+vo.name+"','"+vo.stat_time_str+"'," +
					"	'"+vo.new_related_id+"','"+vo.formula_new.replaceAll("'", "''")+"'," +
					"	"+vo.cal_value+","+vo.pay_value+","+vo.put_value+"," +
					"	"+vo.duty_id+",'"+DateTools.dtsJustDate(new Date())+"',0)";
				updateData(conn, updateSql);
			}
//			freeConnection(conn);
			vo.status = null;
//			System.out.println("接下来更新个人月的数据计算奖金和发放奖金   new DispenseEmployeeImpl().updateByNewFunction(vo)");
			System.out.println("科室一次分配统计结束！");
//			new DispenseEmployeeImpl().updateByNewFunction(vo);
			return vo;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	public DispenseOrganizationVo recalByNewFunction(//
			DispenseOrganizationVo vo) {
		try{
			System.out.println("以最新方式计算单元奖金，将重新计算合并单元奖金。");
			if(vo.new_related_id == null || vo.new_related_id.equals("") ){
				DispenseOrganizationVo rtVo = new DispenseOrganizationVo();
				rtVo.status = "请检查相关工作绩效核算科室是否配置";
				return rtVo;
			}
//			if( vo.formula_new == null || vo.formula_new.equals("")){
//				DispenseOrganizationVo rtVo = new DispenseOrganizationVo();
//				rtVo.status = "请检查相关工作绩效核算科室一次分配计算公式是否配置";
//				return rtVo;
//			}
			
			String newRelatedId = vo.new_related_id.replaceAll("::", ":");
			String[] s= newRelatedId.split(":");
			if(s.length <= 0){
				DispenseOrganizationVo rtVo = new DispenseOrganizationVo();
				rtVo.status = "请检查相关工作绩效核算科室是否配置";
				return rtVo;
			}
			
			String stat_time = vo.stat_time_str;
			
			Connection conn = getConnect();
			String sql1 = "select dbo.GetFullOrganizationName(a.id) as fName," +
					"	a.base_bonus_function_detail as new_function," +
					"	a.duty_id" +
					"	from t_sys_stat_department  as a" +
					"	where a.id = ";
			BigDecimal bt = new BigDecimal("0");
			String totalFormula = "";
			
			for(int i =0;i<s.length;i++){
				if(s[i].equals("")){
					continue;
				}
				OrganizationFirstBonusVo ovo = new OrganizationFirstBonusVo();
				ovo.organization_id = Integer.valueOf(s[i]);
				ResultSet rs = queryData(conn, sql1+ovo.organization_id);
				if(rs.next()){
					ovo.full_name = rs.getString("fName");
					ovo.new_function = rs.getString("new_function");
					if(ovo.new_function != null && !ovo.new_function.equals("")){
						totalFormula += ovo.new_function+"+";
					}					
					ovo.duty_id = rs.getInt("duty_id");					
				}
				ovo.stat_time = DateTools.stdJustDate(stat_time);
				OrganizationFirstBonusVo rtVo = new OrganizationFirstBonusImpl().recalByNewFunction(conn,ovo,1);
				if(rtVo == null){
					System.out.println("没有找到关联科室！！  可检查DispenseFirstBonusImpl");
					continue;
				}
				String rtValue = rtVo.cal_value;
				if(rtValue != null && !rtValue.equals("")){
					System.out.println(ovo.full_name+"   "+ovo.organization_id+"    "+rtValue);
					bt = bt.add(new BigDecimal(rtValue));
				}else{
					System.out.println(ovo.full_name+"   "+ovo.organization_id+"    NULL?");
					bt.add(new BigDecimal("0"));
				}
			}
//			System.out.println(bt.toString());
			
			vo.cal_value = bt.toString();
			if(totalFormula == null || "".equals(totalFormula))
			{
				vo.status = "没有设置公式，请检查后重新计算。";
				return vo;
			}
			vo.formula_new = totalFormula.substring(0, totalFormula.length()-1);
			
			String sql = "select * from t_stat_dispense_month" +
					"	where dispense_id = "+vo.id+
					"	and stat_time = '"+vo.stat_time_str+"'";
			PreparedStatement pst = 
				conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			ResultSet rsx = pst.executeQuery();
			if(rsx.next()){
//======================================SQL2005				
//				if(rsx.getInt("valid") == 0){
//					rsx.updateString("related_organization_id", vo.new_related_id+"");
//					rsx.updateString("formula", vo.formula_new.replaceAll("'", "''"));
//					rsx.updateBigDecimal("cal_value", bt);
//					rsx.updateBigDecimal("pay_value", bt);
//					rsx.updateBigDecimal("put_value", bt);
//					rsx.updateInt("duty_id", vo.duty_id);
//				}else{
//					rsx.updateString("related_organization_id", vo.new_related_id+"");
//					rsx.updateString("formula", vo.formula_new.replaceAll("'", "''"));
//					rsx.updateBigDecimal("cal_value", bt);
//				}
//				rsx.updateRow();
//======================================SQL2000					
				String updateSql = "";
				if(rsx.getInt("valid") == 0){
					updateSql = "update t_stat_dispense_month set related_organization_id = '"+vo.new_related_id+"'," +
							"formula = '"+vo.formula_new.replaceAll("'", "''")+"'," +
							"cal_value="+bt+"," +
							"pay_value="+bt+"," +
							"put_value="+bt+"," +
							"duty_id="+vo.duty_id+
							" where stat_time = '"+vo.stat_time_str+"' and dispense_id = "+vo.id; 
					updateData(conn, updateSql);
				}else{
					updateSql = "update t_stat_dispense_month set related_organization_id = '"+vo.new_related_id+"'," +
						" formula = '"+vo.formula_new.replaceAll("'", "''")+"'," +
						" cal_value="+bt+"," +
						" where stat_time = '"+vo.stat_time_str+"' and dispense_id = "+vo.id; 
					updateData(conn, updateSql);
				}
				
			}else{
				String updateSql = "insert into t_stat_dispense_month" +
					"	(dispense_id,name,stat_time," +
					"	related_organization_id,formula," +
					"	cal_value,pay_value,put_value," +
					"	duty_id,create_time,valid) " +
					"	values("+vo.id+",'"+vo.name+"','"+vo.stat_time_str+"'," +
					"	'"+vo.new_related_id+"','"+vo.formula_new.replaceAll("'", "''")+"'," +
					"	"+vo.cal_value+","+vo.cal_value+","+vo.cal_value+"," +
					"	"+vo.duty_id+",'"+DateTools.dtsJustDate(new Date())+"',0)";
				updateData(conn, updateSql);
			}
			freeConnection(conn);
			vo.status = null;
//			System.out.println("接下来更新个人月的数据计算奖金和发放奖金   new DispenseEmployeeImpl().updateByNewFunction(vo)");
			System.out.println("科室一次分配统计结束！");
//			new DispenseEmployeeImpl().updateByNewFunction(vo);
			return vo;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public DispenseOrganizationVo recalByOldFunction(
			DispenseOrganizationVo vo) {
		// TODO Auto-generated method stub
		return null;
	}
	
	public void validDispenseData(DispenseOrganizationVo vo) {
		try{
			Connection conn = getConnect();
			String sql = "update t_stat_dispense_month set valid = 1" +
					"	where stat_time = '"+vo.stat_time_str+"'" +
					"	and dispense_id = "+vo.id;
			int x = updateData(conn,sql);
			if(x == 1){
				new WorkLoadMoveToBack().workLoatMoveToBackByOrganizationId(vo.stat_time_str, vo.id);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
	public int closeAccount(String ymd) {
		// TODO Auto-generated method stub
		try
		{
			Connection conn = getConnect();
			String sql = "";
			ResultSet rs;
			sql = "select count(1) from closed_t_stat_dispense_month where stat_time = '"+ymd+"'";
			rs = queryData(conn,sql);
			if(rs.next())
			{
				if(rs.getInt(1) > 0)
				{
					return 1;
				}
			}
//			else
//			{
				sql = "delete closed_t_stat_dispense_month  where stat_time = '"+ymd+"'";
				updateData(conn,sql);
				sql = "insert into closed_t_stat_dispense_month" +
						" select * from t_stat_dispense_month" +
						" where stat_time = '"+ymd+"'";
				updateData(conn,sql);
//			}
//			
//			sql = "select count(1) from closed_t_stat_department_month where stat_time = '"+ymd+"'";
//			rs = queryData(conn,sql);
//			if(rs.next())
//			{
//				if(rs.getInt(1) > 0)
//				{
//					return 0;
//				}
//			}
//			else
//			{
				sql = "delete closed_t_stat_department_month  where stat_time = '"+ymd+"'";
				updateData(conn,sql);
				sql = "insert into closed_t_stat_department_month" +
						" select * from t_stat_department_month" +
						" where stat_time = '"+ymd+"'";
				updateData(conn,sql);
//			}
//			
//			sql = "select count(1) from closed_LINGDAO_VALUE where stat_time = '"+ymd+"'";
//			rs = queryData(conn,sql);
//			if(rs.next())
//			{
//				if(rs.getInt(1) > 0)
//				{
//					return 0;
//				}
//			}
//			else
//			{
				sql = "delete closed_LINGDAO_VALUE  where stat_time = '"+ymd+"'";
				updateData(conn,sql);
				sql = "insert into closed_LINGDAO_VALUE" +
						" select * from LINGDAO_VALUE" +
						" where stat_time = '"+ymd+"'";
				updateData(conn,sql);
//			}
//			
//			sql = "select count(1) from close_vote_value where stat_time = '"+ymd+"'";
//			rs = queryData(conn,sql);
//			if(rs.next())
//			{
//				if(rs.getInt(1) > 0)
//				{
//					return 0;
//				}
//			}
//			else
//			{
				sql = "delete closed_vote_value where stat_time = '"+ymd+"'";
				updateData(conn,sql);
				sql = "insert into closed_vote_value" +
						" select * from VOTE_VALUE" +
						" where stat_time = '"+ymd+"'";
				updateData(conn,sql);
//			}
//			
//			sql = "select count(1) from closed_JIANGCHENG_VALUE where stat_time = '"+ymd+"'";
//			rs = queryData(conn,sql);
//			if(rs.next())
//			{
//				if(rs.getInt(1) > 0)
//				{
//					return 0;
//				}
//			}
//			else
//			{
				sql = "delete closed_JIANGCHENG_VALUE  where stat_time = '"+ymd+"'";
				updateData(conn,sql);
				sql = "insert into closed_JIANGCHENG_VALUE" +
						" select * from JIANGCHENG_VALUE" +
						" where stat_time = '"+ymd+"'";
				updateData(conn,sql);
//			}
			freeConnection(conn);
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return 0;
	}
	public List<String> getCloseAccountList()
	{
		try
		{
			List<String> l = new ArrayList();
			Connection conn = getConnect();
			String sql = "select distinct stat_time from closed_t_stat_department_month order by stat_time";
			ResultSet rs = queryData(conn,sql);
			while(rs.next())
			{
				l.add(rs.getString(1));
			}
			return l;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return null;
	}
}
